USE [SONALIKA_BGK_UPDATED]
GO

/****** Object:  StoredProcedure [dbo].[SpTractorSalesReport]    Script Date: 05/29/2014 00:31:46 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SpTractorSalesReport]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[SpTractorSalesReport]
GO

USE [SONALIKA_BGK_UPDATED]
GO

/****** Object:  StoredProcedure [dbo].[SpTractorSalesReport]    Script Date: 05/29/2014 00:31:46 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE PROC [dbo].[SpTractorSalesReport]--'1-1-2013','1-1-2015',0
(@FROM_DATE DATETIME,
@TO_DATE DATETIME,
@TRACTOR_MODEL_ID INT)
AS
BEGIN
IF (@TRACTOR_MODEL_ID <>0)
BEGIN
SELECT SI.SALES_INVOICE_ID
	    ,replace(convert(NVARCHAR, INVOICE_DATE, 106), ' ', '-')AS INVOICE_DATE  
       ,INVOICE_GRANDTOTAL
       ,CUSTOMER_NAME
       ,DC_NO
       ,TM.TRACTOR_MODEL_ID AS MODEL
    
       
 FROM SALES_INVOICES SI
  LEFT OUTER JOIN CUSTOMER C ON C.CUSTOMER_ID=SI.CUSTOMER_ID
  LEFT OUTER JOIN DELIVERY_CHALAN DC ON DC.SALES_INVOICE_ID=SI.SALES_INVOICE_ID
  LEFT OUTER JOIN TRACTOR_PURCHASES TP ON TP.TRACTOR_ID=DC.TRACTOR_ID
  LEFT OUTER JOIN TRACTOR_MODELS TM ON TM.TRACTOR_MODEL_ID=TP.TRACTOR_MODEL_ID
  
  
      
 WHERE SELL_TYPE=(SELECT MASTER_ID FROM MASTER WHERE MASTER_VALUE='TRACTOR')AND (INVOICE_DATE BETWEEN @FROM_DATE AND @TO_DATE) 
       AND TM.TRACTOR_MODEL_ID=@TRACTOR_MODEL_ID

END
ELSE
BEGIN
SELECT SI.SALES_INVOICE_ID
	    ,replace(convert(NVARCHAR, INVOICE_DATE, 106), ' ', '-')AS INVOICE_DATE  
       ,INVOICE_GRANDTOTAL
       ,CUSTOMER_NAME
       ,DC_NO
       ,'0' AS MODEL 
    
       
 FROM SALES_INVOICES SI
  LEFT OUTER JOIN CUSTOMER C ON C.CUSTOMER_ID=SI.CUSTOMER_ID
  LEFT OUTER JOIN DELIVERY_CHALAN DC ON DC.SALES_INVOICE_ID=SI.SALES_INVOICE_ID
      
 WHERE SELL_TYPE=(SELECT MASTER_ID FROM MASTER WHERE MASTER_VALUE='TRACTOR')AND (INVOICE_DATE BETWEEN @FROM_DATE AND @TO_DATE)
END

END







GO


